﻿namespace SupplyChainWeb.Models
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Text;
    using System.Data.Entity;

    /// <summary>
    /// Defines the <see cref="E10DataQuqery" />
    /// </summary>
    public class E10DataQuqery
    {
        /// <summary>
        /// The GetOrders
        /// </summary>
        /// <returns>The <see cref="DataTable"/></returns>
        public static DataSet GetOrders()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT Customer.CustID,Customer.Name,OrderHed.Company,OrderHed.OrderNum,OrderHed.PONum,OrderDtl.IUM, ");
            sb.Append(" OrderDtl.OrderLine,OrderDtl.PartNum,LineDesc,OrderDtl.DocUnitPrice,OrderDtl.OrderQty,OrderHed.OrderDate,OrderDtl.RequestDate,OrderDtl.NeedByDate,OrderDtl.POLine ");
            sb.Append(" FROM Erp.OrderHed ");
            sb.Append(" INNER JOIN Erp.OrderDtl ON OrderDtl.Company = OrderHed.Company AND OrderDtl.OrderNum = OrderHed.OrderNum ");
            sb.Append(" INNER JOIN Erp.Customer ON Customer.Company = OrderHed.Company AND Customer.CustNum = OrderHed.CustNum ");
            sb.Append(" WHERE 1 = 1 and OrderHed.Company = ''+@Company+'' ");
            sb.Append(" ORDER BY OrderHed.OrderNum,OrderDtl.OrderLine");
            String sql = sb.ToString();
            SqlParameter sp = new System.Data.SqlClient.SqlParameter("@Company", "EPIC06");
            DataTable dt = SQLHelper.ExcuteTable(sql, CommandType.Text, sp);
            dt.TableName = "Order";
            dt.Namespace = "Order";
            DataSet ds = new DataSet("OrderDataSet");
            ds.Tables.Add(dt);
             
            return ds;
        }

        public static DataTable GetOrders2()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT Customer.CustID,Customer.Name,OrderHed.Company,OrderHed.OrderNum,OrderHed.PONum, ");
            sb.Append(" OrderDtl.OrderLine,OrderDtl.PartNum,LineDesc,OrderDtl.DocUnitPrice,OrderDtl.OrderQty,OrderHed.OrderDate,OrderDtl.RequestDate,OrderDtl.NeedByDate,OrderDtl.POLine,OrderDtl.IUM, ");
            sb.Append(" OrderTrack.Start,OrderTrack.UserID1,OrderTrack.StartDate,OrderTrack.Finish,OrderTrack.UserID2,OrderTrack.FinishDate,OrderTrack.Ship,OrderTrack.UserID3,OrderTrack.ShipDate ");
            sb.Append(" FROM Erp.OrderHed ");
            sb.Append(" INNER JOIN Erp.OrderDtl ON OrderDtl.Company = OrderHed.Company AND OrderDtl.OrderNum = OrderHed.OrderNum ");
            sb.Append(" LEFT JOIN SCMData.dbo.OrderTrack OrderTrack ON OrderTrack.Company = OrderDtl.Company AND OrderTrack.OrderNum =OrderDtl.OrderNum AND OrderTrack.OrderLine = OrderDtl.OrderLine ");
            sb.Append(" INNER JOIN Erp.Customer ON Customer.Company = OrderHed.Company AND Customer.CustNum = OrderHed.CustNum ");
            sb.Append(" WHERE 1 = 1 and OrderHed.Company = ''+@Company+'' ");
            sb.Append(" ORDER BY OrderHed.OrderNum,OrderDtl.OrderLine");
            String sql = sb.ToString();
            SqlParameter sp = new System.Data.SqlClient.SqlParameter("@Company", "EPIC06");
            DataTable dt = SQLHelper.ExcuteTable(sql, CommandType.Text, sp);
            dt.TableName = "Order";
            dt.Namespace = "Order";
            //DataSet ds = new DataSet("OrderDataSet");
            //ds.Tables.Add(dt);


            //List<OrderModel> os = new List<OrderModel>(dt.Rows.Count);
            //foreach (DataRow dr in dt.Rows)
            //{
            //    os.Add(ToEntity(dr));
            //}


            //foreach (DataRow row in dt.Rows)
            //{
            //    OrderModel order = new OrderModel();
            //    foreach (DataColumn col in dt.Columns)
            //    {
            //        if ("CustID".Equals(col.ColumnName))
            //        {
            //            order.CustID =row[col.ColumnName].ToString();
            //        }
            //        else if ("Name".Equals(col.ColumnName))
            //        {
            //            order.Name = row[col.ColumnName].ToString();
            //        }
            //        else if ("Company".Equals(col.ColumnName))
            //        {
            //            order.Company =Convert.ToString( row[col.ColumnName]);
            //        }
            //        else if ("OrderNum".Equals(col.ColumnName))
            //        {
            //            order.OrderNum = Convert.ToInt32(row[col.ColumnName]);
            //        }
            //        else if ("PONum".Equals(col.ColumnName))
            //        {
            //            order.PONum = row[col.ColumnName].ToString();
            //        }
            //        else if ("OrderLine".Equals(col.ColumnName))
            //        {
            //            order.OrderLine = Convert.ToInt32(row[col.ColumnName]);
            //        }
            //        else if ("PartNum".Equals(col.ColumnName))
            //        {
            //            order.PartNum = row[col.ColumnName].ToString();
            //        }
            //        else if ("LineDesc".Equals(col.ColumnName))
            //        {
            //            order.LineDesc = row[col.ColumnName].ToString();
            //        }
            //        else if ("DocUnitPrice".Equals(col.ColumnName))
            //        {
            //            order.DocUnitPrice = Convert.ToDecimal(row[col.ColumnName]);
            //        }
            //        else if ("OrderQty".Equals(col.ColumnName))
            //        {
            //            order.OrderQty = Convert.ToDecimal(row[col.ColumnName]);
            //        }
            //        else if ("OrderDate".Equals(col.ColumnName))
            //        {
            //            order.OrderDate =  Convert.ToDateTime( row[col.ColumnName].ToString().Length>0? row[col.ColumnName]:"2018-01-01");
            //        }
            //        else if ("RequestDate".Equals(col.ColumnName))
            //        {
            //            order.RequestDate =  Convert.ToDateTime( row[col.ColumnName].ToString().Length>0? row[col.ColumnName]:"2018-01-01");
            //        }
            //        else if ("NeedByDate".Equals(col.ColumnName))
            //        {
            //            order.NeedByDate = Convert.ToDateTime( row[col.ColumnName].ToString().Length>0? row[col.ColumnName]:"2018-01-01");
            //        }
            //        else if ("POLine".Equals(col.ColumnName))
            //        {
            //            order.POLine = Convert.ToInt32( row[col.ColumnName].ToString().Length>0?row[col.ColumnName]:0);
            //        }
            //        else if ("IUM".Equals(col.ColumnName))
            //        {
            //            order.IUM = row[col.ColumnName].ToString();
            //        }
            //        else if ("Start".Equals(col.ColumnName))
            //        {
            //            order.Start = Convert.ToBoolean( row[col.ColumnName].ToString().Length>0?row[col.ColumnName]:false);
            //        }
            //        else if ("Finish".Equals(col.ColumnName))
            //        {
            //            order.Finish =Convert.ToBoolean( row[col.ColumnName].ToString().Length>0?row[col.ColumnName]:false);
            //        }
            //        else if ("Start".Equals(col.ColumnName))
            //        {
            //            order.Ship =Convert.ToBoolean( row[col.ColumnName].ToString().Length>0?row[col.ColumnName]:false);
            //        }


            //    }

            //    os.Add(order);
            //}


            //foreach (DataRow row in dt.Rows)
            //{
            //    dynamic dobj = new System.Dynamic.ExpandoObject();
            //    var dic = (IDictionary<string, dynamic>)dobj; //定义另外一个别名变量. 然后进行动态赋值..
            //    foreach (DataColumn col in dt.Columns)
            //    {
            //        dic[col.ColumnName] = row[col];// 上面不定义别名这里就不能用dic[]
            //    }
            //    os.Add((OrderModel)dobj);
            //}

            //List<dynamic> os = new List<dynamic>();
            //foreach (DataRow row in dt.Rows)
            //{
            //    dynamic dobj = new System.Dynamic.ExpandoObject();
            //    var dic = (IDictionary<string, dynamic>)dobj; //定义另外一个别名变量. 然后进行动态赋值..
            //    foreach (DataColumn col in dt.Columns)
            //    {
            //        dic[col.ColumnName] = row[col];// 上面不定义别名这里就不能用dic[]
            //    }
            //    os.Add(dobj);
            //}


            return dt;
        }



        ///// <summary>
        ///// 将DataRow行转换成Entity
        ///// </summary>
        ///// <param name="dr"></param>
        ///// <returns></returns>
        //public static T ToEntity(DataRow dr)
        //{
        //    T entity = new T();
        //    Type info = typeof(T);
        //    var members = info.GetMembers();
        //    foreach (var mi in members)
        //    {
        //        if (mi.MemberType == MemberTypes.Property)
        //        {
        //            //读取属性上的DataField特性
        //            object[] attributes = mi.GetCustomAttributes(typeof(DataFieldAttribute), true);
        //            foreach (var attr in attributes)
        //            {
        //                var dataFieldAttr = attr as DataFieldAttribute;
        //                if (dataFieldAttr != null)
        //                {
        //                    var propInfo = info.GetProperty(mi.Name);
        //                    if (dr.Table.Columns.Contains(dataFieldAttr.ColumnName))
        //                    {
        //                        //根据ColumnName，将dr中的相对字段赋值给Entity属性
        //                        propInfo.SetValue(entity,
        //                                          Convert.ChangeType(dr[dataFieldAttr.ColumnName], propInfo.PropertyType),
        //                                          null);
        //                    }

        //                }
        //            }
        //        }
        //    }
        //    return entity;
        //}


    }
}
